package com.hysoft.service.Impl;



import com.hysoft.service.ImportService;
import com.hysoft.util.LogUtil;
import com.hysoft.util.Msg;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class ImportServiceImpl implements ImportService {

    @Override
    public List<String> findAllAccTable(Connection accessConn) {
        List<String> list=new ArrayList<String>();
        PreparedStatement ps=null;
        ResultSet rs=null;
        DatabaseMetaData dbmd1=null;
        String tablename="";
        try {
            String[] types = {"TABLE"};//数组变量types
            String tt, tp;

            dbmd1 = accessConn.getMetaData();
            rs = dbmd1.getTables(null, null, null, types);
            while (rs.next()){
                tablename = rs.getString(3);
                list.add(tablename);
            }

        } catch (SQLException e) {
            try {
                LogUtil.inserCorrectLog("In",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }

        return list;
    }
    @Override
    public Msg importAccToOracle(Connection oracleConn, Connection accessConn, String owner) {
        PreparedStatement ps=null;
        ResultSet rs=null;
        PreparedStatement ps1=null;
        ResultSet rs1=null;
        PreparedStatement ps2=null;
        ResultSet rs2=null;
        PreparedStatement ps3=null;
        ResultSet rs3=null;
        PreparedStatement ps4=null;
        ResultSet rs4=null;
        PreparedStatement ps5=null;
        ResultSet rs5=null;
        PreparedStatement ps6=null;
        ResultSet rs6=null;
        PreparedStatement ps7=null;
        ResultSet rs7=null;
        PreparedStatement ps8=null;
        ResultSet rs8=null;
        PreparedStatement ps9=null;
        ResultSet rs9=null;
        PreparedStatement ps10=null;
        ResultSet rs10=null;

        DatabaseMetaData dbmd=null;
        DatabaseMetaData dbmd1=null;

        ResultSetMetaData resultSetMetaData=null;

        String OracleName="";
        String ColumnName="";
        String tablename="";
        String toDateType="";
        String keySameStr="";
        String valueSameStr="";
        String isNullType[]=null;
        int count=0;

        int sun=0;
        int totalTableCount=0;
        Map<String,String> mapSAME=null;

        try{
            String[] types = {"TABLE"};//数组变量types
            String tt, tp;

            //查询Access所有表个数
            dbmd= accessConn.getMetaData();
            rs = dbmd.getTables(null, null, null, types);
            while (rs.next()) {
                totalTableCount++;
            }

            System.out.println(totalTableCount + "所有表");

            //查询oracle表个数
            //String sql="select distinct 主键表 from (select rownum,主键表 from (select DISTINCT 主键表 from v_pk1 where 外键拥有者=主键拥有者 and 主键拥有者="+"'"+owner+"'"+") union select rownum+100,外键表 from (select DISTINCT 外键表 from v_pk1 where 外键拥有者=主键拥有者 and 外键拥有者="+"'"+owner+"'"+") union select rownum+200,t.table_name from user_tables t inner join user_tab_comments f on t.table_name = f.table_name and t.TABLE_NAME not in(select DISTINCT 主键表 from v_pk1 where 外键拥有者=主键拥有者 and 主键拥有者="+"'"+owner+"'"+"  union select DISTINCT 外键表 from v_pk1 where 外键拥有者=主键拥有者 and 外键拥有者="+"'"+owner+"'"+")) ";
            String sql="select * from ODTAVLE";
            //String sql = "select * from all_tables where owner='WL_SCK'";
            ps1 = oracleConn.prepareStatement(sql);
            System.out.println(sql);
            rs1 = ps1.executeQuery();
            dboracle:while (rs1.next()) {
                OracleName = rs1.getString(2);
                System.out.println(OracleName);
                //查询Access所有表
                dbmd1 = accessConn.getMetaData();
                rs2 = dbmd.getTables(null, null, null, types);
                dbAccess:while (rs2.next()) {

                    tablename = rs2.getString(3);
                    System.out.println(tablename);
                    //用来存放目标表中不存在的字段

                    if (OracleName.equals(tablename)) {
                        Map<String,String> map1 = null;
                        Map<String,String> map=compareType(oracleConn,accessConn,tablename);
                        map1 = map;
                        d : if(map.size()==0){
                            //查询数据是否存在

                            /**
                             *获取Oracle数据库中的主键
                             */
                            Integer count1 = 0;
                            String sqlPrimaryKey = "select c.column_name from all_constraints a,all_cons_columns c where  a.constraint_name=c.constraint_name(+) and a.owner=c.owner(+) and  a.constraint_type='P' and a.TABLE_NAME=" + "'" + tablename + "'" + "and a.owner=" + "'" + owner + "'" + " order by position";
                            ps3 = oracleConn.prepareStatement(sqlPrimaryKey);
                            rs3= ps3.executeQuery();

                            String sqlPrimaryKeyCount = "select count(c.column_name) from all_constraints a,all_cons_columns c where  a.constraint_name=c.constraint_name(+) and a.owner=c.owner(+) and  a.constraint_type='P' and a.TABLE_NAME=" + "'" + tablename + "'" + "and a.owner=" + "'" + owner + "'" + " order by position";
                            ps4 = oracleConn.prepareStatement(sqlPrimaryKeyCount);
                            rs4 = ps4.executeQuery();
                            while (rs4.next()) {
                                count = rs4.getInt(1);
                            }
                            /**
                             * 使用数据存储主键名称和主键类型
                             */
                            String primaryKeyStr[] = new String[count];
                            String primaryKeyType[] = new String[count];
                            int mid = 0;
                            int typeMid = 0;
                            /**
                             * 获取主键字段加入数组
                             */
                            while (rs3.next()) {
                                primaryKeyStr[mid++] = rs3.getString(1).toString();

                            }
                            /**
                             * 获取主键类型
                             */
                            for(int i=0;i<count;i++){
                                String  getPrimaryKey = "select data_type  from all_tab_columns ut where ut.table_name="+"'"+tablename+"'" +"and owner="+"'"+owner+"'"+" and column_name="+"'"+primaryKeyStr[i]+"'";
                                ps4 = oracleConn.prepareStatement(getPrimaryKey);
                                rs4 = ps4.executeQuery();
                                while(rs4.next()){
                                    primaryKeyType[typeMid++] = rs4.getString(1).toString();
                                }

                            }

                            String sql12 = "select * from " + tablename;
                            ps5 = accessConn.prepareStatement(sql12);
                            rs5 = ps5.executeQuery();

                            do1:while (rs5.next()) {
                                //ResultSet resultSet1=resultSet;
                                String strPrimary = "";
                                for (int m = 0; m < count; m++) {
                                    strPrimary += rs5.getString(primaryKeyStr[m]) + ",";
                                }
                                String newStr[] = strPrimary.substring(0, strPrimary.length() - 1).split(",");
                                resultSetMetaData= rs5.getMetaData();
                                String param = "";
                                String value = "";
                                String parames = "";
                                /**
                                 * 拼接sql去access数据库中查找是否存在数据
                                 */
                                for (int j = 0; j < count; j++) {
                                    if(primaryKeyType[j].equals("NUMBER")){
                                        parames += primaryKeyStr[j]+"="+newStr[j] +" and"+" ";
                                    }
                                    else if(primaryKeyType[j].equals("DATE")){
                                        String str = newStr[j].toString();
                                        String str1 = str.substring(0, 10);
                                        parames += primaryKeyStr[j]+"=" + str1  +" and"+" ";
                                    }
                                    else{
                                        parames += primaryKeyStr[j]+"="+"'"+newStr[j]+"'"+" and"+" ";
                                    }

                                }
                                String newParam = parames.substring(0, parames.length() - 4);
                                /**
                                 * 查询数据是否存在
                                 */
                                String sqlYesOrNoExit = "select * from" + " " + tablename + " " + "where" + " " + newParam;
                                ps6= oracleConn.prepareStatement(sqlYesOrNoExit);
                                rs6 = ps6.executeQuery();
                                /**
                                 * 如果没有数据，则插入数据
                                 */
                                while (!(rs6.next())) {
                                    mapSAME=compareSame(oracleConn,accessConn,tablename);
                                    String keySame ="";
                                    String valueSame="";
                                    for(Map.Entry<String,String> same:mapSAME.entrySet()){
                                        keySame +=same.getKey()+",";
                                        valueSame +=same.getValue()+",";
                                    }

                                    keySameStr=keySame.substring(0,keySame.length()-1);
                                    //valueSameStr=valueSame.substring(0,valueSame.length()-1);

                                    String s1 = "to_date('','yyyy-mm-dd hh24:mi:ss')";

                                    /**
                                     * oracle数据库中字段是否为空
                                     */
                                    isNullType= new String[mapSAME.size()];
                                    /**
                                     * oracle数据库字段是否为空
                                     */
                                    int isNullCount = 0;
                                    boolean flag=false;
                                    String isNotNullSql = "select COLUMN_NAME,NULLABLE from user_tab_columns t where t.table_name="+"'"+tablename+"'"+" and t.nullable='N'";
                                    ps7 = oracleConn.prepareStatement(isNotNullSql);
                                    rs7 = ps7.executeQuery();
                                    while(rs7.next()){
                                        isNullType[isNullCount++] = rs7.getString(1);
                                    }
                                    rs7.close();
                                    ps7.close();

//                                        String s=resultSet.getString(isNullType[1]);
//                                        System.out.println(s);

                                    ps8=accessConn.prepareStatement(sqlYesOrNoExit);
                                    rs8=ps8.executeQuery();
                                    while (rs8.next()){

                                        for (int i=0;i<isNullCount;i++) {
                                            String  a=isNullType[i];
                                            System.out.println(a);
                                            String acc=rs8.getString(isNullType[i]);
                                            if(acc==null){
                                                flag=false;
                                                break ;
                                            }
                                            else {
                                                flag=true;
                                            }
                                        }
                                        if(flag) {
                                            ps9=accessConn.prepareStatement(sqlYesOrNoExit);
                                            rs9=ps9.executeQuery();
                                            String ColumnTypeName="";
                                            String ColumnDateTypeName="";
                                            String time = "to_date('','yyyy-mm-dd hh24:mi:ss')";
                                            while (rs9.next()){
                                                for (Map.Entry<String, String> same : mapSAME.entrySet()) {
                                                    valueSame = same.getValue();
                                                    String cs = rs9.getString(same.getKey());
                                                    if (valueSame.equalsIgnoreCase("DATE")) {
                                                        if(cs!=null) {
                                                            ColumnDateTypeName += cs;
                                                            StringBuffer sb = new StringBuffer(time);
                                                            sb.insert(9, ColumnDateTypeName);
                                                            ColumnTypeName += sb.toString() + ",";
                                                        }else {
                                                            ColumnTypeName +=null + ",";
                                                        }
                                                    } else if (valueSame.equalsIgnoreCase("NUMBER")) {
                                                        if(cs!=null) {
                                                            ColumnTypeName += cs + ",";
                                                        }else {
                                                            ColumnTypeName +=null+",";
                                                        }
                                                    } else if (valueSame.equalsIgnoreCase("VARCHAR2")) {
                                                        if(cs!=null) {
                                                            ColumnTypeName += "'" + cs + "'" + ",";
                                                        }else {
                                                            ColumnTypeName += "'" + null + "'" + ",";
                                                        }
                                                    }
                                                }
                                            }
                                            rs9.close();
                                            ps9.close();
                                            System.out.println(ColumnName + "" + ColumnTypeName);
                                            //String ColumnNameStr = ColumnName.substring(0, ColumnName.length() - 1);
                                            String ColumnTypeNameStr = ColumnTypeName.substring(0, ColumnTypeName.length() - 1);

                                            String insertSql = "insert into" + " " + tablename + " " + "(" + keySameStr + ")" + "values(" + ColumnTypeNameStr + ") ";
                                            ps10 = oracleConn.prepareStatement(insertSql);
                                            System.out.println(insertSql);
                                            int insertCount = ps10.executeUpdate();

                                            if (insertCount > 0) {
                                                System.out.println("插入成功"+(++sun));
                                                LogUtil.inserCorrectLog("In",LogUtil.InfoWithSuccess("oracle",tablename,keySameStr,ColumnTypeNameStr),"ok.txt");
                                            }else{
                                                LogUtil.inserCorrectLog("In",LogUtil.InfoWithError("oracle",tablename,keySameStr,ColumnTypeNameStr),"error.txt");
                                            }
                                            ps10.close();
                                        }

                                        break ;
                                    }
                                    rs8.close();
                                    ps8.close();
                                    continue do1;
                                }
                                rs6.close();
                                ps6.close();
                            }
                            rs5.close();
                            ps5.close();
                        }else {
                            String notExistFields = "";
                            for (Object m:map1.entrySet()){
                                notExistFields += ((Map.Entry) m).getKey()+" ";
                            }
                            LogUtil.inserCorrectLog("In",LogUtil.InfoWithDiffFields("oracle",tablename,notExistFields),"error.txt");
                            break ;
                        }
                    } else {
                        System.out.println("匹配不合格");
                        continue dbAccess;
                    }
                }
            }

        } catch (SQLException e) {
            try {
                LogUtil.inserCorrectLog("In",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(rs7 != null){
                try {
                    rs7.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs4 != null){
                try {
                    rs4.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs3 != null){
                try {
                    rs3.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } if(rs2 != null){
                try {
                    rs2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } if(rs1!= null){
                try {
                    rs1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } if(rs!= null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps8 != null){
                try {
                    ps8.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps7 != null){
                try {
                    ps7.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps4 != null){
                try {
                    ps4.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps3 != null){
                try {
                    ps3.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps2 != null){
                try {
                    ps2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps1 != null){
                try {
                    ps1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(accessConn != null){
                try {
                    accessConn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(oracleConn != null){
                try {
                    oracleConn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }


        return Msg.success();
    }

    @Override
    public Map<String, String> compareType(Connection oracleConn, Connection accessConn, String tableName) throws SQLException {
        Map<String,String> oracleMap = new HashMap<>();
        Map<String,String> accessMap = new HashMap<>();
        String accessSql = "select * from"+" "+tableName;
        String oracleSql = "select A.COLUMN_NAME,A.DATA_TYPE  from user_tab_columns A where TABLE_NAME= "+"'"+tableName+"'";
        PreparedStatement oraclePstmt = null;
        PreparedStatement accessPstmt = null;
        ResultSet oracleResultSet = null;
        ResultSet accessResultSet = null;
        ResultSetMetaData accessResultMetaData = null;
        PreparedStatement isNotTypePstmt = null;
        ResultSet isNotResultSet = null;
        Map<String,String> differentStr=null;
        try {
            oraclePstmt = oracleConn.prepareStatement(oracleSql);
            oracleResultSet = oraclePstmt.executeQuery();
            /**
             * 将oracle数据库中的字段和类型加入map
             */
            while (oracleResultSet.next()){
                oracleMap.put(oracleResultSet.getString(1),oracleResultSet.getString(2));
            }
            oracleResultSet.close();
            oraclePstmt.close();

            accessPstmt = accessConn.prepareStatement(accessSql);
            accessResultSet = accessPstmt.executeQuery();
            accessResultMetaData = accessResultSet.getMetaData();
            /**
             * 将access数据库中的字段和类型加入map
             */
            while(accessResultSet.next()){
                System.out.println(accessResultMetaData.getColumnCount());
                for(int i=1;i<=accessResultMetaData.getColumnCount();i++){
                    if(accessResultMetaData.getColumnType(i) == 12 ||accessResultMetaData.getColumnType(i) == -1)
                        accessMap.put(accessResultMetaData.getColumnLabel(i),"VARCHAR2");
                    else if(accessResultMetaData.getColumnType(i) == 4)
                        accessMap.put(accessResultMetaData.getColumnLabel(i),"NUMBER");
                    else if(accessResultMetaData.getColumnType(i) == 93)
                        accessMap.put(accessResultMetaData.getColumnLabel(i),"DATE");
                }
                break;
            }
            accessResultSet.close();
            accessPstmt.close();

            /**
             * 字段和类型完全相同的字段存放的数组
             */
            Map<String,String> sameStr = new HashMap<>();
            /**
             * 字段和类型不相同的字段存放的数组
             */
            differentStr = new HashMap<>();
            /**
             * oracle数据库中字段是否为空
             */
            String isNullType[] = new String[oracleMap.size()];
            /**
             * oracle数据库字段是否为空
             */
            int isNullCount = 0;
            //String isNullSql = "select COLUMN_NAME,NULLABLE from user_tab_columns t where t.table_name="+"'"+tableName+"'"+" and t.nullable='Y'";
            String isNotNullSql = "select COLUMN_NAME,NULLABLE from user_tab_columns t where t.table_name="+"'"+tableName+"'"+" and t.nullable='N'";
            isNotTypePstmt = oracleConn.prepareCall(isNotNullSql);
            isNotResultSet = isNotTypePstmt.executeQuery();
            while(isNotResultSet.next()){
                isNullType[isNullCount++] = isNotResultSet.getString(1);
            }
            isNotResultSet.close();
            isNotTypePstmt.close();
            Boolean flag = true;
            for (Map.Entry<String,String> oracleEntry:oracleMap.entrySet()){
                for (Map.Entry<String,String> accessEntry:accessMap.entrySet())
                    if(oracleEntry.getKey().equalsIgnoreCase(accessEntry.getKey()) && oracleEntry.getValue().equalsIgnoreCase(accessEntry.getValue())){
                        sameStr.put(oracleEntry.getKey(),oracleEntry.getValue());
                        flag = false;
                        break;
                    }
                if(flag){
                    differentStr.put(oracleEntry.getKey(),oracleEntry.getValue());
                }
                flag = true;
            }
        } catch (Exception e) {
            try {
                LogUtil.inserCorrectLog("In",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return differentStr;
    }

    @Override
    public Map<String, String> compareSame(Connection oracleConn, Connection accessConn, String tableName) throws SQLException {
        Map<String,String> oracleMap = new HashMap<>();
        Map<String,String> accessMap = new HashMap<>();
        String accessSql = "select * from"+" "+tableName;
        String oracleSql = "select A.COLUMN_NAME,A.DATA_TYPE  from user_tab_columns A where TABLE_NAME= "+"'"+tableName+"'";
        PreparedStatement oraclePstmt = null;
        PreparedStatement accessPstmt = null;
        ResultSet oracleResultSet = null;
        ResultSet accessResultSet = null;
        ResultSetMetaData accessResultMetaData = null;
        PreparedStatement isNotTypePstmt = null;
        ResultSet isNotResultSet = null;
        Map<String,String> differentStr=null;
        Map<String,String> sameStr=null;
        try {
            oraclePstmt = oracleConn.prepareStatement(oracleSql);
            oracleResultSet = oraclePstmt.executeQuery();
            /**
             * 将oracle数据库中的字段和类型加入map
             */
            while (oracleResultSet.next()){
                oracleMap.put(oracleResultSet.getString(1),oracleResultSet.getString(2));
            }
            oracleResultSet.close();
            oraclePstmt.close();

            accessPstmt = accessConn.prepareStatement(accessSql);
            accessResultSet = accessPstmt.executeQuery();
            accessResultMetaData = accessResultSet.getMetaData();
            /**
             * 将access数据库中的字段和类型加入map
             */
            while(accessResultSet.next()){
                System.out.println(accessResultMetaData.getColumnCount());
                for(int i=1;i<=accessResultMetaData.getColumnCount();i++){
                    if(accessResultMetaData.getColumnType(i) == 12 ||accessResultMetaData.getColumnType(i) == -1)
                        accessMap.put(accessResultMetaData.getColumnLabel(i),"VARCHAR2");
                    else if(accessResultMetaData.getColumnType(i) == 4)
                        accessMap.put(accessResultMetaData.getColumnLabel(i),"NUMBER");
                    else if(accessResultMetaData.getColumnType(i) == 93)
                        accessMap.put(accessResultMetaData.getColumnLabel(i),"DATE");
                }
                break;
            }
            accessResultSet.close();
            accessPstmt.close();
            /**
             * 字段和类型完全相同的字段存放的数组
             */
            sameStr = new HashMap<>();
            /**
             * 字段和类型不相同的字段存放的数组
             */
            differentStr = new HashMap<>();
            /**
             * oracle数据库中字段是否为空
             */
            String isNullType[] = new String[oracleMap.size()];
            /**
             * oracle数据库字段是否为空
             */
            int isNullCount = 0;
            //String isNullSql = "select COLUMN_NAME,NULLABLE from user_tab_columns t where t.table_name="+"'"+tableName+"'"+" and t.nullable='Y'";
            String isNotNullSql = "select COLUMN_NAME,NULLABLE from user_tab_columns t where t.table_name="+"'"+tableName+"'"+" and t.nullable='N'";
            isNotTypePstmt = oracleConn.prepareCall(isNotNullSql);
            isNotResultSet = isNotTypePstmt.executeQuery();
            while(isNotResultSet.next()){
                isNullType[isNullCount++] = isNotResultSet.getString(1);
            }
            isNotResultSet.close();
            isNotTypePstmt.close();

            Boolean flag = true;
            for (Map.Entry<String,String> oracleEntry:oracleMap.entrySet()){
                for (Map.Entry<String,String> accessEntry:accessMap.entrySet())
                    if(oracleEntry.getKey().equalsIgnoreCase(accessEntry.getKey()) && oracleEntry.getValue().equalsIgnoreCase(accessEntry.getValue())){
                        sameStr.put(oracleEntry.getKey(),oracleEntry.getValue());
                        flag = false;
                        break;
                    }
                if(flag){
                    differentStr.put(oracleEntry.getKey(),oracleEntry.getValue());
                }
                flag = true;
            }
        } catch (Exception e) {
            try {
                LogUtil.inserCorrectLog("In",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return sameStr;
    }
}
